<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>Section 12.7.&nbsp; Performing a Subquery</title>
<link rel="STYLESHEET" type="text/css" href="images/style.css">
<link rel="STYLESHEET" type="text/css" href="images/docsafari.css">
<script type="text/javascript"><!--
google_ad_client = "pub-0203281046321155";
google_alternate_ad_url = "http://www.bookhtml.com/adbrite.htm";
google_ad_width = 728;
google_ad_height = 90;
google_ad_format = "728x90_as";
google_ad_type = "text_image";
google_ad_channel ="4867465545";
google_color_border = "FFFFFF";
google_color_link = "0000FF";
google_color_bg = "FFFFFF";
google_color_text = "000000";
google_color_url = "0000FF";
//--></script>
<script type="text/javascript"
  src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12-SECT-6.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12-SECT-8.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<br><table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><TD valign="top"><a name="learnphpmysql-CHP-12-SECT-7"></a>
<h3 id="title-IDARTMAJ" class="docSection1Title">12.7. Performing a Subquery</h3>
<a name="IDX-CHP-12-0602"></a> 
<a name="IDX-CHP-12-0603"></a> 

<p class="docText">Sometimes you'll want to display the data in a linked table as a list instead of repeating all of the values from the joined table. For example, when listing books, it would look nicer to list authors in one cell of your table. <a class="docLink" href="#learnphpmysql-CHP-12-EX-12">Example 12-12</a> uses a second query and a loop to accomplish this.</p>
<a name="learnphpmysql-CHP-12-EX-12"></a><h5 id="title-IDAIUMAJ" class="docExampleTitle">Example 12-12. Displaying the authors in a list</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
&lt;?php
require_once('db_login.php');
require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: &lt;br /&gt;". DB::errorMessage($connection));
}
// Display the table
$query = "SELECT * FROM `books`";
$result = $connection-&gt;query($query);
if (DB::isError($result)){
die("Could not query the database: &lt;br /&gt;".$query." ".DB::errorMessage($result));
}
echo '&lt;table border="1"&gt;';
echo "&lt;tr&gt;&lt;th&gt;Title&lt;/th&gt;&lt;th&gt;Pages&lt;/th&gt;&lt;th&gt;Authors&lt;/th&gt;&lt;/tr&gt;";
while ($result_row = $result-&gt;fetchRow(DB_FETCHMODE_ASSOC)) {
echo "&lt;tr&gt;&lt;td&gt;";
echo htmlentities($result_row["title"]) . '&lt;/td&gt;&lt;td&gt;';
echo htmlentities($result_row["pages"]) . '&lt;/td&gt;&lt;td&gt;';
<span class="docEmphStrong">$author_query = "SELECT * FROM `authors` WHERE `title_id`
=".$result_row["title_id"];$author_result = $connection-&gt;query($author_query);</span>
if (DB::isError($author_result)){
die("Could not query the database: &lt;br /&gt;".$author_query."
".DB::errorMessage($author_result));
}
<span class="docEmphStrong">$author_count = $author_result-&gt;numRows();
if (0 == $author_count) {
echo 'none';
}
$counter = 0;
while ($author_result_row = $author_result-&gt;fetchRow(DB_FETCHMODE_ASSOC)) {
$counter++;
echo htmlentities($author_result_row["author"]);
if ($counter != $author_count) {
echo ', ';
}
}</span>
echo '&lt;/td&gt;&lt;/tr&gt;';
}
echo '&lt;/table&gt;';
$connection-&gt;disconnect();
?&gt;
</pre><br>

</td></tr></table></p>
<p class="docText">Define a second query and result set for the authors. For each title, a query of the <tt>authors</tt> table can retrieve a variable number of authors. Count the result set using the <tt>numRows</tt> function. To avoid an empty cell, if there were no authors, you display <tt>None</tt>. Use the <tt>$author_count</tt> variable again while looping so as not to put a comma after the last author name in the list. The result is this nicer format, shown in <a class="docLink" href="#learnphpmysql-CHP-12-FIG-14">Figure 12-14</a>.</P>
<a name="learnphpmysql-CHP-12-FIG-14"></a><p><center>
<h5 class="docFigureTitle">Figure 12-14. Authors displayed on a single line</H5>
<img border="0" alt="" id="195131084202" width="549" height="198" SRC="images/learnphpmysql_1214.jpg">
</center></P><br>
<p class="docText">In <a class="docLink" href="learnphpmysql-CHP-13.html#learnphpmysql-CHP-13">Chapter 13</a>, we'll talk about storing information in sessions and how to limit access to pages.</P>

</TD></TR></table>
<br>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12-SECT-6.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12-SECT-8.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
</body></html>
